#!/usr/bin/python
#encoding=utf-8

import pymssql
import datetime
import re
import xlwt
import smtplib

nowTime = datetime.datetime.now().strftime('%Y-%m-%d')
#当月1号
monthFirst = datetime.date.today().replace(day=1)

today = datetime.date.today()
#nowTime = '2017-10-08'

conn = pymssql.connect('172.22.8.112', 'sa', 'P2ss.w0rd', 'ICCO', port='49156')
cursor = conn.cursor()
htmlTable=''

eatListWorkBook = xlwt.Workbook(encoding = 'utf-8')
for i in range((today - monthFirst).days + 1):
	day = monthFirst + datetime.timedelta(days=i)
	ALLSheet = eatListWorkBook.add_sheet(str(day)+'当天所有记录')
	lunchSheet = eatListWorkBook.add_sheet(str(day)+'午餐记录')
	dinnerSheet = eatListWorkBook.add_sheet(str(day)+'晚餐记录')
	nightFoodSheet = eatListWorkBook.add_sheet(str(day)+'夜宵记录')



	sqlAll = "SELECT Employee.emp_id, Employee.card_id,Employee.emp_fname,Departs.depart_name,TimeRecords.sign_time,Clocks.Clock_name FROM TimeRecords LEFT JOIN Employee ON TimeRecords.join_id = Employee.join_id LEFT JOIN Departs on TimeRecords.depart_id = Departs.depart_id  LEFT JOIN Clocks ON TimeRecords.clock_id = Clocks.Clock_id WHERE sign_time >= '" + str(day) + " 00:00:00' AND employee.emp_id != '' AND sign_time <= '"+ str(day)+ " 23:59:59';"
	sqlLunch = "SELECT Employee.emp_id, Employee.card_id,Employee.emp_fname,Departs.depart_name,TimeRecords.sign_time,Clocks.Clock_name FROM TimeRecords LEFT JOIN Employee ON TimeRecords.join_id = Employee.join_id LEFT JOIN Departs on TimeRecords.depart_id = Departs.depart_id  LEFT JOIN Clocks ON TimeRecords.clock_id = Clocks.Clock_id WHERE sign_time >= '" + str(day) + " 10:50:00' AND employee.emp_id != '' AND sign_time <= '"+ str(day) +  " 13:30:00';"
	sqlDinner = "SELECT Employee.emp_id, Employee.card_id,Employee.emp_fname,Departs.depart_name,TimeRecords.sign_time,Clocks.Clock_name FROM TimeRecords LEFT JOIN Employee ON TimeRecords.join_id = Employee.join_id LEFT JOIN Departs on TimeRecords.depart_id = Departs.depart_id  LEFT JOIN Clocks ON TimeRecords.clock_id = Clocks.Clock_id WHERE sign_time >= '" + str(day) + " 15:50:00' AND employee.emp_id != '' AND sign_time <= '"+ str(day) +  " 18:30:00';"
	sqlNightFood = "SELECT Employee.emp_id, Employee.card_id,Employee.emp_fname,Departs.depart_name,TimeRecords.sign_time,Clocks.Clock_name FROM TimeRecords LEFT JOIN Employee ON TimeRecords.join_id = Employee.join_id LEFT JOIN Departs on TimeRecords.depart_id = Departs.depart_id  LEFT JOIN Clocks ON TimeRecords.clock_id = Clocks.Clock_id WHERE sign_time >= '" + str(day) + " 22:20:00' AND employee.emp_id != '' AND sign_time <= '"+ str(day) +  " 23:59:59';"

	sqlLunchWJ = "SELECT Employee.emp_id, Employee.card_id,Employee.emp_fname,Departs.depart_name,TimeRecords.sign_time,Clocks.Clock_name FROM TimeRecords LEFT JOIN Employee ON TimeRecords.join_id = Employee.join_id LEFT JOIN Departs on TimeRecords.depart_id = Departs.depart_id  LEFT JOIN Clocks ON TimeRecords.clock_id = Clocks.Clock_id WHERE sign_time >= '" + str(day) + " 10:50:00' AND employee.emp_id != '' AND sign_time <= '"+ str(day) +  " 13:30:00' AND (employee.emp_id lIKE 'A%' or employee.emp_id LIKE 'K%');"
	sqlDinnerWJ = "SELECT Employee.emp_id, Employee.card_id,Employee.emp_fname,Departs.depart_name,TimeRecords.sign_time,Clocks.Clock_name FROM TimeRecords LEFT JOIN Employee ON TimeRecords.join_id = Employee.join_id LEFT JOIN Departs on TimeRecords.depart_id = Departs.depart_id  LEFT JOIN Clocks ON TimeRecords.clock_id = Clocks.Clock_id WHERE sign_time >= '" + str(day) + " 15:50:00' AND employee.emp_id != '' AND sign_time <= '"+ str(day) +  " 18:30:00'AND (employee.emp_id lIKE 'A%' or employee.emp_id LIKE 'K%');"
	sqlNightFoodWJ = "SELECT Employee.emp_id, Employee.card_id,Employee.emp_fname,Departs.depart_name,TimeRecords.sign_time,Clocks.Clock_name FROM TimeRecords LEFT JOIN Employee ON TimeRecords.join_id = Employee.join_id LEFT JOIN Departs on TimeRecords.depart_id = Departs.depart_id  LEFT JOIN Clocks ON TimeRecords.clock_id = Clocks.Clock_id WHERE sign_time >= '" + str(day) + " 22:20:00' AND employee.emp_id != '' AND sign_time <= '"+ str(day) +  " 23:59:59'AND (employee.emp_id lIKE 'A%' or employee.emp_id LIKE 'K%');"

	sqlLunchSZ = "SELECT Employee.emp_id, Employee.card_id,Employee.emp_fname,Departs.depart_name,TimeRecords.sign_time,Clocks.Clock_name FROM TimeRecords LEFT JOIN Employee ON TimeRecords.join_id = Employee.join_id LEFT JOIN Departs on TimeRecords.depart_id = Departs.depart_id  LEFT JOIN Clocks ON TimeRecords.clock_id = Clocks.Clock_id WHERE sign_time >= '" + str(day) + " 10:50:00' AND employee.emp_id != '' AND sign_time <= '"+ str(day) +  " 13:30:00' AND employee.emp_id lIKE 'S%';"
	sqlDinnerSZ = "SELECT Employee.emp_id, Employee.card_id,Employee.emp_fname,Departs.depart_name,TimeRecords.sign_time,Clocks.Clock_name FROM TimeRecords LEFT JOIN Employee ON TimeRecords.join_id = Employee.join_id LEFT JOIN Departs on TimeRecords.depart_id = Departs.depart_id  LEFT JOIN Clocks ON TimeRecords.clock_id = Clocks.Clock_id WHERE sign_time >= '" + str(day) + " 15:50:00' AND employee.emp_id != '' AND sign_time <= '"+ str(day) +  " 18:30:00'AND employee.emp_id lIKE 'S%';"
	sqlNightFoodSZ = "SELECT Employee.emp_id, Employee.card_id,Employee.emp_fname,Departs.depart_name,TimeRecords.sign_time,Clocks.Clock_name FROM TimeRecords LEFT JOIN Employee ON TimeRecords.join_id = Employee.join_id LEFT JOIN Departs on TimeRecords.depart_id = Departs.depart_id  LEFT JOIN Clocks ON TimeRecords.clock_id = Clocks.Clock_id WHERE sign_time >= '" + str(day) + " 22:20:00' AND employee.emp_id != '' AND sign_time <= '"+ str(day) +  " 23:59:59'AND employee.emp_id lIKE 'S%';"

	cursor.execute(sqlAll)
	allList = cursor.fetchall()

	cursor.execute(sqlLunch)
	lunchList = cursor.fetchall()

	cursor.execute(sqlDinner)
	dinnerList = cursor.fetchall()

	cursor.execute(sqlNightFood)
	nightFoodList = cursor.fetchall()

	cursor.execute(sqlLunchWJ)
	lunchWJList = cursor.fetchall()

	cursor.execute(sqlDinnerWJ)
	dinnerWJList = cursor.fetchall()

	cursor.execute(sqlNightFoodWJ)
	nightFoodWJList = cursor.fetchall()

	cursor.execute(sqlLunchSZ)
	lunchSZList = cursor.fetchall()

	cursor.execute(sqlDinnerSZ)
	dinnerSZList = cursor.fetchall()

	cursor.execute(sqlNightFoodSZ)
	nightFoodSZList = cursor.fetchall()


	htmlTable +=  '''
      <tr>
        <td class="tg-baqh">'''+str(day)+'''</td>
        <td class="tg-baqh">'''+str(len(lunchList))+'''</td>
        <td class="tg-baqh">'''+str(len(dinnerList))+'''</td>
        <td class="tg-baqh">'''+str(len(nightFoodList))+'''</td>
        <td class="tg-baqh">'''+str(len(lunchWJList))+'''</td>
        <td class="tg-baqh">'''+str(len(dinnerWJList))+'''</td>
        <td class="tg-baqh">'''+str(len(nightFoodWJList))+'''</td>
        <td class="tg-baqh">'''+str(len(lunchSZList))+'''</td>
        <td class="tg-baqh">'''+str(len(dinnerSZList))+'''</td>
        <td class="tg-baqh">'''+str(len(nightFoodSZList))+'''</td>
      </tr>'''



	line = 0
	for detailAll in allList:
		column = 0
		for i in detailAll:
			ALLSheet.write(line, column, i)
			column+=1
		line+=1


	line = 0
	for detailLunch in lunchList:
		column = 0
		for i in detailLunch:
			lunchSheet.write(line, column, i)
			column+=1
		line+=1

	line = 0
	for detailDinner in dinnerList:
			column = 0
			for i in detailDinner:
					dinnerSheet.write(line, column, i)
					column+=1
			line+=1

	line = 0
	for detailNightFood in nightFoodList:
			column = 0
			for i in detailNightFood:
					nightFoodSheet.write(line, column, i)
					column+=1
			line+=1


eatListWorkBook.save("meal.xls")
conn.close()

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header

mail_host="spam.avita.com.tw"  #设置服务器
mail_user="tao_lu"    #用户名
mail_pass="P2ss.w0rd"   #口令 

sender = 'tao_lu@avita.com.tw'
receivers = ['tao_lu@avita.com.tw']

message = MIMEMultipart()
message['From'] = Header("AutoMail")
message['To'] =  Header("CheckMan")
subject = '餐厅刷卡记录'
message['Subject'] = Header(subject, 'utf-8')

htmlContent = '''<html>
	<head>
		
		<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
		<meta http-equiv="content-type" content="text/html;charset=utf-8">
		
		
<title>就餐统计信息</title>

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;border-color:#aabcfe;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aabcfe;color:#669;background-color:#e8edff;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aabcfe;color:#039;background-color:#b9c9fe;}
.tg .tg-baqh{text-align:center;vertical-align:top}
.tg .tg-amwm{font-weight:bold;text-align:center;vertical-align:top}
</style>
</head>
<body>
<table class="tg">
  <tr>
    <th class="tg-amwm">日期</th>
    <th class="tg-amwm">中餐</th>
    <th class="tg-amwm">晚餐</th>
    <th class="tg-amwm">夜宵</th>
    <th class="tg-amwm">中餐(吴江)</th>
    <th class="tg-amwm">晚餐(吴江)</th>
    <th class="tg-amwm">夜宵(吴江)</th>
    <th class="tg-amwm">中餐(苏州)</th>
    <th class="tg-amwm">晚餐(苏州)</th>
    <th class="tg-amwm">夜宵(苏州)</th>
  </tr>
  ''' + htmlTable + '''</table></body>'''


#邮件正文内容
message.attach(MIMEText(htmlContent, 'html', 'utf-8'))
 
# 构造附件1，传送当前目录下的 test.txt 文件
att1 = MIMEText(open('meal.xls', 'rb').read(), 'base64', 'utf-8')
att1["Content-Type"] = 'application/octet-stream'
# 这里的filename可以任意写，写什么名字，邮件中显示什么名字
att1["Content-Disposition"] = 'attachment; filename="餐厅刷卡记录.xls"'
message.attach(att1)
 


try:
	smtpObj = smtplib.SMTP()
	smtpObj.connect(mail_host, 25)    # 25 为 SMTP 端口号
	smtpObj.login(mail_user,mail_pass)  
	smtpObj.sendmail(sender, receivers, message.as_string())
	print "邮件发送成功"
except smtplib.SMTPException:
    	print "Error: 无法发送邮件"




